package com.example.luozizhao.eprxdemo.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.luozizhao.eprxdemo.commom.App;
import com.example.luozizhao.eprxdemo.utils.SPManager;

import org.xutils.DbManager;
import org.xutils.db.DbModelSelector;
import org.xutils.db.Selector;
import org.xutils.db.sqlite.WhereBuilder;
import org.xutils.db.table.DbModel;
import org.xutils.db.table.TableEntity;
import org.xutils.ex.DbException;
import org.xutils.x;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;


/**
 * 本地数据库（数据缓存）
 * 
 * @author yekaihe
 *
 */
public class LocalDbHelper {
	private static final String TAG = LocalDbHelper.class.getName();
	
	private static final int PAGE_SIZE = 20;
	
	private static final int USER = 0;
	
	private static final int SYSTEM = 1;
	
	private static final Object writeLock = new Object();

	// 用户库实例
	private static volatile LocalDbHelper userInstance;
	// 系统库实例
	private static volatile LocalDbHelper systemInstance;
	
	protected Context mContext;
	protected DbManager.DaoConfig daoConfig = new DbManager.DaoConfig();
	private int initUser;

	private LocalDbHelper(Context context, int type) {
		mContext = context.getApplicationContext();
		daoConfig = new DbManager.DaoConfig();
		String dbName = null;
		if (type == USER) {
		    initUser = SPManager.Profile.getUid();
		    // 获取当前登录用户id，命名：用户id_epolice.db
		    dbName = initUser + "_epolice.db";
		} else if (type == SYSTEM) {
		    dbName = "system_epolice.db";
		}
		daoConfig.setDbName(dbName);
		daoConfig.setDbVersion(1);
	}

	/**
	 * 获取epolice.db操作实例
	 * 按照用户初始化数据库后，在系统登录前后可能会出现数据库不一致的场景
	 * @param ctx
	 * @return
	 */
	public static LocalDbHelper getUserInstance(Context ctx) {
		if (userInstance == null || SPManager.Profile.getUid() != userInstance.initUser) {
		    synchronized (writeLock) {
		        if (userInstance == null || SPManager.Profile.getUid() != userInstance.initUser) {
		            userInstance = new LocalDbHelper(ctx, USER);
		        }
            }
		}
		return userInstance;
	}
	
	public static LocalDbHelper getSystemInstance(Context ctx) {
	    if (systemInstance == null) {
	        synchronized (writeLock) {
                if (systemInstance == null) {
                    systemInstance = new LocalDbHelper(ctx, SYSTEM);
                }
            }
	    }
	    return systemInstance;
	}
	
	/**
	 * @return
	 */
	public static LocalDbHelper getUserInstance(){
		return getUserInstance(App.applicationContext);
	}
	
	public static LocalDbHelper getSystemInstance(){
        return getSystemInstance(App.applicationContext);
    }
	
	/**
	 * 删除数据库
	 * 备注：由于数据库按照用户设计，此处要清理多个用户库
	 */
	public void clear(Context ctx){
		try {
			x.getDb(daoConfig).dropDb();
		} catch (DbException e) {
			Log.e(TAG, String.format("drop database failed, epolice.db"));
		}
	}

	/**
	 * 查询指定表的总数
	 *
	 * @param clz
	 * @return
	 */
	public <T> Long count(Class<T> clz, WhereBuilder where) {
		try {
			return x.getDb(daoConfig).selector(clz).where(where).count();
		} catch (DbException e) {
			Log.e(TAG, String.format("count all data, entity: %s, error: %s", clz.getName(), e.getMessage()));
			return 0L;
		}
	}

	public void close(){
		try {
			x.getDb(daoConfig).close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询指定表的所有数据
	 * 
	 * @param clz
	 * @return
	 */
	public <T> List<T> findAll(Class<T> clz) {
		try {
			return x.getDb(daoConfig).selector(clz).findAll();
		} catch (DbException e) {
			Log.e(TAG, String.format("find all data, entity: %s, error: %s", clz.getName(), e.getMessage()));
			return new ArrayList<T>();
		}
	}
	
	public <T> void clearAll(Class<T> clz) {
		try {
			x.getDb(daoConfig).dropTable(clz);
		} catch (DbException e) {
			Log.e(TAG, String.format("drop table, entity: %s, error: %s", clz.getName(), e.getMessage()));
		}
	}

	/**
	 * 
	 * @param
	 */
	public <T> void save(T bean) {
		try {
		    synchronized (writeLock) {
		        x.getDb(daoConfig).save(bean);
            }
		} catch (DbException e) {
			Log.e(TAG, e.getMessage());
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
		}
	}

	public <T> void update(T bean) {
        try {
            synchronized (writeLock) {
                x.getDb(daoConfig).update(bean);
            }
        } catch (DbException e) {
			Log.e("papa","DbException保存失败"+e.getMessage());
            Log.e(TAG, null==e.getMessage()?"":e.getMessage());
        } catch (Exception e) {
			Log.e("papa","Exception保存失败"+e.getMessage());
            Log.e(TAG, null==e.getMessage()?"":e.getMessage());
        }
    }
	
	/**
	 *
	 * @param list
	 */
	public <T> void save(List<T> list) {
		DbException dbe = null;
		String entityType = null;
		int errCount = 0;
		synchronized (writeLock) {
		    for (T entity : list) {
		        try {
		            x.getDb(daoConfig).save(entity);
		        } catch (DbException e) {
		            // Log e
		            errCount ++;
		            dbe = e;
		            if(entityType == null){
		                entityType = entity.getClass().getName();
		            }
		        } catch (Exception e) {
		            Log.e(TAG, e.getMessage());
		        }
		    }
        }

		//避免循环中记录日志，在此处记录一条
		if(errCount > 0){
			Log.e(TAG, String.format("save list data, entity:%s, count:%d, error: %s", entityType, errCount, dbe.getMessage()));
		}
	}

	/**
	 *
	 * @param list
	 */
	public <T> void saveAll(List<T> list) {
		if (list == null || list.size() == 0) {
			return;
		}
		synchronized (writeLock) {
		    SQLiteDatabase db  = x.getDb(daoConfig).getDatabase();
		    db.beginTransaction();
		    try {
		        for (T entity : list) {
		            x.getDb(daoConfig).save(entity);
		        }
		        db.setTransactionSuccessful();
		    } catch (DbException e) {
		        Log.e(TAG, e.getMessage());
		    } catch (Exception e) {
		        Log.e(TAG, e.getMessage());
		    } finally {
		        db.endTransaction();
		    }
        }
	}


	
	/**
	 * 保存列表（失败后继续）
	 * @param list
	 */
	public <T> void saveOrUpdate(List<T> list) {
		DbException dbe = null;
		String entityType = null;
		int errCount = 0;
		synchronized (writeLock) {
		    for (T entity : list) {
		        try {
		            x.getDb(daoConfig).saveOrUpdate(entity);
		        } catch (DbException e) {
		            // Log e
		            errCount ++;
		            dbe = e;
		            if(entityType == null){
		                entityType = entity.getClass().getName();
		            }
		        } catch (Exception e) {
		            Log.e(TAG, e.getMessage());
		        }
		    }
        }
		
		//避免循环中记录日志，在此处记录一条
		if(errCount > 0){
			Log.e(TAG, String.format("save list data, entity:%s, count:%d, error: %s", entityType, errCount, dbe.getMessage()));
		}
	}

	/**
	 * 分页操作本地库记录
	 * @param page
	 * @param clz	数据库对应的实体类别
	 * @param orderCol 逆序字段
	 * @return
	 */
	public <T> List<T> find(int page, Class<T> clz, String orderCol) {
		return find(page, PAGE_SIZE, clz, orderCol);
	}

	/**
	 * 分页查找
	 * @param page 第几页
	 * @param pageSize 分页大小
	 * @param clz 数据表实体
	 * @param orderCol 逆序字段
	 * @return
	 */
	public <T> List<T> find(int page, int pageSize, Class<T> clz, String orderCol) {
		synchronized (this) {
			List<T> result = null;
			try {
				//选择数据库实体
				Selector<T> selector = x.getDb(daoConfig).selector(clz);
				
				//设置分页
				selector.limit(pageSize).offset((page * 20));
				
				//设置逆序字段
				if(orderCol != null){
					selector.orderBy(orderCol, true);
				}
				
				//查询结果
				result = selector.findAll();
			} catch (DbException e) {
				Log.e(TAG, String.format("find page data, page:%d, page size:%d, entity: %s, order column: %s, error: %s", page, pageSize, clz.getName(), orderCol, e.getMessage()));
			}

			//如果没有结果，或者异常情况下，则返回空列表对象
			if (result == null) {
				result = new ArrayList<T>();
			}

			return result;
		}
	}

	/**
	 * 删除指定对象的本地记录
	 * @param t
	 */
	public <T> void delete(T t) {
		try {
		    synchronized (writeLock) {
		        x.getDb(daoConfig).delete(t);
            }
		} catch (DbException e) {
			Log.e(TAG, String.format("delete data, object: %s, error: %s", String.valueOf(t), e.getMessage()));
		}
	}


	public <T> void delete(Class<T> clz, WhereBuilder where) {
		try {
			x.getDb(daoConfig).delete(clz, where);
		} catch (DbException e) {
			Log.e(TAG, String.format("delete data, object: %s, error: %s", clz, e.getMessage()));
		}
	}


	/**
	 * 分页操作本地库记录
	 * @param page
	 * @param clz	数据库对应的实体类别
	 * @param orderCol 逆序字段
	 * @return
	 */
	public <T> List<T> find(int page, Class<T> clz, String orderCol, int type) {
		return find(page, PAGE_SIZE, clz, orderCol,type);
	}

	/**
	 * 模糊查询指定表的所有数据
	 *(用于查询本地联系人)
	 * @param clz
	 * 			创建表的具体bean类
	 * @param key
	 * 			查询的字段
	 * @return
	 * 			查询到的bean对象集合
	 */
	public <T> List<T> findAll(Class<T> clz, String key) {
		try {
			return x.getDb(daoConfig).selector(clz).where("uname", "like", "%" + key + "%").
					or("job_name", "like", "%" + key + "%").
					or("org_name", "like", "%" + key + "%").
					or("mobile", "like", "%" + key + "%").
					findAll();
		} catch (DbException e) {
			Log.e(TAG, String.format("find all data, entity: %s, error: %s", clz.getName(), e.getMessage()));
			return new ArrayList<T>();
		}
	}


	public <T> List<T> findAll(Class<T> clz, WhereBuilder where) {
		try {
			Selector<T> selector = x.getDb(daoConfig).selector(clz);
			if(null!=where){
				selector.where(where);
			}

			return selector.findAll();
		} catch (DbException e) {
			Log.e(TAG, String.format("find all data, entity: %s, error: %s", clz.getName(), e.getMessage()));
			return new ArrayList<T>();
		}
	}








	/**
	 *
	 * @param page  第几页
	 * @param pageSize 分页大小
	 * @param clz   数据表实体
	 * @param orderCol 逆序字段
	 * @param type  数据类型
	 */
	public <T> List<T> find(int page, int pageSize, Class<T> clz, String orderCol, int type) {
		synchronized (this) {
			List<T> result = null;
			try {
				//选择数据库实体
				Selector<T> selector = x.getDb(daoConfig).selector(clz);

				//设置分页
				selector.limit(20).offset((page * 20));
				selector.where("type","=",type);
				//设置逆序字段
				if(orderCol != null){
					selector.orderBy(orderCol, true);
				}

				//查询结果
				result = selector.findAll();
			} catch (DbException e) {
				Log.e(TAG, String.format("find page data, page:%d, page size:%d, entity: %s, order column: %s, error: %s", page, pageSize, clz.getName(), orderCol, e.getMessage()));
			}

			//如果没有结果，或者异常情况下，则返回空列表对象
			if (result == null) {
				result = new ArrayList<T>();
			}

			return result;
		}
	}



	public <T> void deleteById(Class<T> clz, Object key) {
	    try {
	        synchronized (writeLock) {
	            x.getDb(daoConfig).deleteById(clz, key);
            }
        } catch (DbException e) {
            Log.e(TAG, String.format("deleteById, class: %s, kye: %s, error: %s", clz, key, e.getMessage()));
        }
	}

	public <T> void deleteByWhere(Class<T> clz, WhereBuilder whereBuilder) {
		try {
			synchronized (writeLock) {
				x.getDb(daoConfig).delete(clz,whereBuilder);
			}
		} catch (Exception e) {
			Log.e("tag",e.getMessage());
		}
	}


	/**
	 * 根据主键查询
	 * @param clz
	 * @param key
	 * @return
	 */
	public <T> T findById(Class<T> clz, Object key) {
		try {
			return x.getDb(daoConfig).findById(clz, key);
		} catch (DbException e) {
			Log.e(TAG, String.format("delete data, type:%s, key:%s, error: %s", clz.getName(), String.valueOf(key),
					e.getMessage()));
		}

		return null;
	}
	
	/**
	 * 获取指定实体的最大键值
	 * @param clz
	 * @return
	 */
	public <T> long maxPrimaryKey(Class<T> clz) {
		try {
			Selector<T> selector = x.getDb(daoConfig).selector(clz);
			long maxId = maxPrimaryKey(selector);
			return maxId;
		} catch (DbException e) {
			Log.e(TAG, String.format("get xms max id, error: %s", e.getMessage()));
		}

		return 0;
	}
	
	/**
	 * 获取指定条件下的最大主键值
	 * @param selector
	 * @return
	 * @throws DbException
	 */
	public <T> long maxPrimaryKey(Selector<T> selector) throws DbException {
		TableEntity<T> table = selector.getTable();
		if (!table.tableIsExist())
			return 0;

		DbModelSelector dmSelector = selector.select("max(\"" + table.getId().getName() + "\") as maxid");
		DbModel firstModel = dmSelector.findFirst();
		if (firstModel != null) {
			return firstModel.getLong("maxid");
		}
		return 0;
	}

	public <T> T findFirst(Class<T> clz, WhereBuilder where, String orderByField) {
	    try {
            Selector<T> selector = x.getDb(this.daoConfig).selector(clz);
            if (where != null) {
                selector.where(where);
            }
            selector.orderBy(orderByField, true);
            return selector.findFirst();
        } catch (DbException e) {
            Log.e(TAG, String.format("get last xms, error: %s", e.getMessage()));
            return null;
        } finally {
        }
	}
	
	/**
     * 根据条件查询符合条件的所有数据
     * 
     * @return
     */
    public <T> List<T> queryAll(Class<T> clz, Integer pageNumber, Integer pageSize, WhereBuilder where, String orderCol,
                                boolean desc) {
        List<T> result = null;
        try {
            Selector<T> selector = x.getDb(daoConfig).selector(clz);
            // 设置where条件
            if (where != null) {
                selector.where(where);
            }
            
            // 设置排序
            if(orderCol != null){
                selector.orderBy(orderCol,desc);
            }
            // 设置分页
            if (pageSize != null && pageNumber != null) {
                selector.limit(pageSize).offset(((pageNumber-1) * pageSize));
            }
            result = selector.findAll();
        } catch (DbException e) {
            Log.e(TAG, String.format("find xms, error: %s", e.getMessage()));
        }

        if (result == null) {
            result = new ArrayList<T>();
        }

        return result;
    }
	
    public <T> List<T> queryAll(Class<T> clz) {
        return queryAll(clz, null, null, null, null, false);
    }
    
    public <T> List<T> queryAll(Class<T> clz, Integer pageNumber, Integer pageSize) {
        return queryAll(clz, pageNumber, pageSize, null, null, false);
    }
    
    public <T> List<T> queryAll(Class<T> clz, Integer pageNumber, Integer pageSize, WhereBuilder where) {
        return queryAll(clz, pageNumber, pageSize, where, null, false);
    }
    
    public <T> List<T> queryAll(Class<T> clz, Integer pageNumber, Integer pageSize, String orderCol, boolean desc) {
        return queryAll(clz, pageNumber, pageSize, null, orderCol, desc);
    }
    
    public <T> List<T> queryAll(Class<T> clz, String orderCol, boolean desc) {
        return queryAll(clz, null, null, null, orderCol, desc);
    }
	
	public <T> List<T> queryAll(Class<T> clz, WhereBuilder where, String orderCol, boolean desc) {
		return queryAll(clz, null, null, where, orderCol, desc);
	}
	
	public <T> List<T> queryAll(Class<T> clz, WhereBuilder where) {
        return queryAll(clz, null, null, where, null, false);
    }
	
	public <T> Long getCount(Class<T> clz, WhereBuilder where) {
	    Long result = 0L;
	    try {
            Selector<T> selector = x.getDb(daoConfig).selector(clz);
            if (where != null) {
                selector.where(where);
            }
            result = selector.count();
        } catch (DbException e) {
            Log.e(TAG, String.format("get count, error: %s", e.getMessage()));
        }
        return result;
	}
	
}
